Financial Statements in Power BI
->THIS BLOG HAS AN UPDATED VERSION, CLICK HERE<-
The Missing Generation: Accountants in PowerBI
Custom Financial Statements do not come naturally to Power BI. There’s no way to denote sub-totals, no way to make easy, ordered groupings and no way to set the layout. The options available are generally formatting options applied to entire columns or values based on their comparators ( See: Conditional Formatting ) - never a specific set or subset of Rows. Basic functionality for Accountants is lacking. Judging by the customization options available to use in Matrices and Tables, Paginated reporting in Power BI was never a priority but this challenge has been met with strong integration with Power BI and Excel though features like Analyze In Excel.
This means Accountants are not comfortable in PowerBI. So How do we bring them back?
Hackers gonna’ Hack
That's me..
FreshBI has turned the PowerBI Performance Financials process into something we’re proud of, given the limitations in paginated reporting in Power BI. What used to be out of reach for every accountant is now a reality; custom paginated reports in PowerBI are functional, conceptually easy and fast (depending how deep you venture down the DAX hole)
In a nut-shell, we design a structure that a ‘Display Measure’ can use to decide which formula to should be displayed in the values section of the matrix. Our design structure is a static table ( Excel / ‘In-Memory’ / SQL server ) with at least 3 columns: Index, Grouping, and Formatted Header. Index maintains the order of the structure, Grouping is consumed by the Dax to gather the correct account(s) (Take note that this does NOT have to be one data type. Some Lines could be single accounts, some lines could be whole departments. We’ll get to this later.) and Formatted Header is what we actually see when we’re viewing the report.
Our Dax Formulas start with the ‘Display measure’ which ‘switches’ the output formula. In the ‘Display measure’, we check which formula we should be using and retrieves it to the matrix. Inside a ‘Display measure’, we would probably have 3 ‘Base measures’: Single Account, Low Level Grouping and High-Level Groupings. The Dax is complex, but I’ll do my best to explain and provide examples.
Here are few points highlighting the utility and features of Statements in Power BI
Tailored Statement Layout
Bolds, Italics and underlines are still missing in PBI tables (you can vote for it here), however we can include pipe characters and white space to achieve an indended effect.
Custom Account Grouping
While Groupings are feasible in PowerBI, they require a hierarchal data set. Hierarchal data sets are not inclined to be sorted, or include nice formatting. By using the Structure method, we can just hard code our Grouping sets.
Row-Level Control
We get to decide the rules. Every row can have an ID or Grouping type (L1, L2, L3.. etc). The possibilities are endless.
Time Intelligence
Need MTD? Check. Need QTD? Check. YTD vs PYTD? Easy. This structure does nothing to limit the standard Dax Formulas we’re used to. Not to mention if you set it up properly, it’s contextual as well.
Drill-Through
We can leverage the index keys or grouping keys to Drill Through pages. This means from a P&L we can dive straight in to the GL transactions make up the Drill Through value. We are not covering this in this episode.
Expand & Collapse Layouts
The Grouping keys play a big role in collapsing sub sets of data. We can exclude all single account lines, all Level 3 groupings- whatever the board room calls for, this format can handle it.
So how do we get the Accountants back?
Well we can't get them all back- but a good start is by implementing custom tables in PowerBI.
Here are some steps we overcome in this guided tutorial.
How do I define my layouts ?
How do I define my structure and additional Dax info ?
What Dax measures do we need? ?
How should I structure my Matrices ?
How can I collapse the dataset ?
All of these are answered in the steps below, or the video at the bottom!
I encourage you to follow along! Its quick, easy, and a major step in creating a rich analytical environment.
Step-by-Step Instructions
1) Define the visual side:
We need to define what the Financial statement should actually look like. In this example, you can see I’ve added Groupings, SubTotals, Single accounts and spacings.
2) Define identifying info:
Here you can see three columns have been added: Index, Group Type and Group Set. Index keeps the order when the Header is displayed. Group type defines the level of grouping we’re performing ( if any ) and which Dax Measure to call. Level 0 Indicates a blank row, Level 1 indicates a title row, Level 2 indicates a level two sub total, etc.. Group Set tells the Dax formula which accounts to group. Level 6 Groups are just single accounts wheras Level 2 Groups are all accounts that start with two specific numbers.
3) Bring the table in to PowerBI:
Now that you have a format, it needs to be brought in to PowerBI. I'm importing it as 'Assets Display Table' because this dataset is only for Assets.
4) Dax 200:
We need 6 Dax Measures to make it work. The Display measure switches which measure gets called based on what row is calculating. You can find those measures here..
5) Add a Matrix:
Drop in index and formatted title in to the Row headers and your Calendar Elements to your columns. Set Index to show items without data. Turn off Word Wrap in the Column Headers and Row headers. Turn off Stepped layout and totals as well. It will throw an error till you drop in the display measure. Make sure there are NO relationships between the data and the display table.
6) Final Product:
Throw in the display measure and format to taste. At this point it should be fully functional.
7) Bonus Step: Collapsable Statements:
We know that sub totals are independent from each other and single account rows. That means we can exclude or include any set of our group types. Above we’ve Excluded single account lines.
How Can This Help My Business?
In addition to the points covered earlier on in this blog, there are a few more benefits that I feel should be mentioned.
Cross Business Reports: This is the way forward if you want to build 'perfect' looking reports for every level of your business.
Semi-Dynamic Financials: This method mentioned above mean the totals will never lie!
Accountants in PowerBI: Finally you can tell the accountants there's no excuse! PowerBI has everything they need now!
Thanks for reading and I look forward to posting with you again.
Our Latest Blogs
About FreshBI
Based in USA and Canada, we have helped hundreds of businesses achieve excellence & success through business intelligence.
Power BI runs in our blood, and we are ready to take your business to next level.